const connection = require('../app/database')

class MomentService {
  async create(userId, content) {
    const statement = `INSERT INTO moment (user_id,content) VALUES (?,?)`
    const result = await connection.execute(statement, [userId, content])
    return result
  }

  async getMomentId(momentId) {
    const statement = `
      SELECT 
       m.id mid,m.content mcontent,m.createAt mcreatAt,m.updateAt mupadateAt,
       JSON_OBJECT('id',u.id,'name',u.name) author,
       (SELECT JSON_ARRAYAGG(
       JSON_OBJECT('id',c.id,'content',c.content,'createAt',c.createAt,'updateAt',c.updateAt,
                    'user',JSON_OBJECT('id',cu.id,'name',cu.name))
       ) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id) comments,
       JSON_ARRAYAGG(
       JSON_OBJECT('id',l.id,'name',l.name)
       ) labels,
       (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/',file.filename)) FROM file WHERE m.id = file.moment_id) images
       FROM moment m
       LEFT JOIN user u ON m.user_id = u.id
       LEFT JOIN moment_label ml ON m.id = ml.moment_id
       LEFT JOIN label l ON l.id = ml.label_id
       WHERE m.id = ?
       GROUP BY m.id
        `
    const [result] = await connection.execute(statement, [momentId])
    return result[0]
  }

  async getMomentList(offset, size) {
    console.log(offset,size)
    const statement = `
     SELECT 
       m.id mid,m.content mcontent,m.createAt mcreatAt,m.updateAt mupadateAt,
       JSON_OBJECT('id',u.id,'name',u.name) author,
       (SELECT JSON_ARRAYAGG(
       JSON_OBJECT('id',c.id,'content',c.content,'createAt',c.createAt,'updateAt',c.updateAt,
                    'user',JSON_OBJECT('id',cu.id,'name',cu.name))
       ) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id) comments,
       JSON_ARRAYAGG(
       JSON_OBJECT('id',l.id,'name',l.name)
       ) labels,
       (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/',file.filename)) FROM file WHERE m.id = file.moment_id) images
       FROM moment m
       LEFT JOIN user u ON m.user_id = u.id
       LEFT JOIN moment_label ml ON m.id = ml.moment_id
       LEFT JOIN label l ON l.id = ml.label_id  
       GROUP BY m.id
       LIMIT ?, ?
    `
    const [result] = await connection.execute(statement, [offset, size])
    // console.log(result)
    return result
  }
  async updateMoment(content,momentId) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?`
    const [result] = await connection.execute(statement,[content,momentId])
    return result
  }

  async deleteMoments(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?`;
    const [result] = await connection.execute(statement, [momentId]);
    return result;
  }

  async hasLabel(momentId,labelId){
    // console.log(momentId,labelId)
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?`
    const [result] = await connection.execute(statement,[momentId,labelId])
    return result[0]
  }

  async addLabel(momentId,labelId){
    // console.log(momentId,labelId)
    const statement = `INSERT INTO moment_label (moment_id,label_id) VALUES (?,?)`
    const [result] = await connection.execute(statement,[momentId,labelId])
    return result
  }

}

module.exports = new MomentService()